'use strict';
const DB = require("../pgsql")
module.exports = (request, response, rfun)=>{
    if(!request.SESSION.userid){
        response.statusCode = 302;
        response.setHeader("location", "/");
        response.end();
        return;
    }
    //console.log(request.SESSION);
    if(!(request.SESSION && (request.POST.productID in request.SESSION))){
        request.LOG.error("productID", request.POST.productID, request.SESSION);
        response.end();
        return;
    }
    switch(request.GET.pathname){
        case "/game/v1/incomeData/incomeNum":{ //概览>游戏概况>收入
            DB.exeSql("SELECT SUM(edec) val FROM devevent WHERE appkey = $1 AND ename='1' AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'",(ret)=>{
                response.DATA = ret.rows[0] && ret.rows[0].val;
                if(!response.DATA) response.DATA = '0';
                //console.log(response.DATA);
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/incomeData/incomeNumList":{ //概览>游戏概况>收入
            DB.exeSql(`SELECT etime::date ite,SUM(edec) val FROM devevent WHERE appkey = $1 AND ename='1' AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' 
            GROUP BY etime::date`,(ret)=>{
                DataTmp(response.DATA=[],request.POST.startTime,request.POST.endTime);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.length;j++){
                        if(cdt == response.DATA[j].item){
                            response.DATA[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/incomeData/chargePlayerNum":{ //概览>游戏概况>付费玩家
            DB.exeSql("SELECT COUNT(DISTINCT eparam) val FROM devevent WHERE appkey = $1 AND ename='1' AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'",(ret)=>{
                response.DATA = ret.rows[0] && ret.rows[0].val;
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/newPlayer/installList":{ //概览>游戏概况>设备激活
            DB.exeSql("SELECT etime::date ite,COUNT(DISTINCT dkey) val FROM devlist WHERE appkey = $1 AND isnew=true AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY etime::date",(ret)=>{
                DataTmp(response.DATA=[],request.POST.startTime,request.POST.endTime);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.length;j++){
                        if(cdt == response.DATA[j].item){
                            response.DATA[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/concurrent/acupcu":{ //概览>游戏概况>ACU&PCU
            DB.exeSql(`SELECT ctime::date ite,SUM(EXTRACT(epoch from utime-ctime))/3600/24 val FROM devonline 
            WHERE appkey = $1 AND ctime >= $2 AND ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
            group by ctime::date`,(ret)=>{
                DataTmp(response.DATA={"acu":[],"pcu":[]},request.POST.startTime,request.POST.endTime);//console.log(ret);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.acu.length;j++){
                        if(cdt == response.DATA.acu[j].item){
                            response.DATA.acu[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                DB.exeSql(`SELECT ctime::date ite,MAX(val) val FROM(
                    SELECT A.ctime,MAX(B.ctime) bctime,A.utime,COUNT(DISTINCT B.account)+1 val from devonline A 
                    inner join devonline B on A.ctime < B.ctime and A.utime > B.ctime AND A.ctime < B.utime
                    WHERE A.appkey=$1 and B.appkey=$1 and A.ctime >= $2 AND A.ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' and B.ctime >= $2 AND B.ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                    group by A.ctime,A.utime
                ) X group by ctime::date`,(ret)=>{ //console.log(ret);
                    for(let i=0;i<ret.rows.length;i++){
                        let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                        for(let j=0;j<response.DATA.pcu.length;j++){
                            if(cdt == response.DATA.pcu[j].item){
                                response.DATA.pcu[j].value = ret.rows[i].val|0;
                                break;
                            }
                        }
                    }
                    rfun();
                },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime])
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/newPlayer/fetchInstallNewPlayerList":{ // 游戏玩家>新增玩家>新增激活和账户
            response.DATA = {"install":[],"newPlayer":[]};
            let dt=request.POST.startTime,bt = new Date("20"+request.POST.startTime.substr(0,2)+"-"+request.POST.startTime.substr(2,2)+"-"+request.POST.startTime.substr(4,2));
            for(let i=0;dt<=request.POST.endTime;i++){
                response.DATA.install[i] = {"item":dt,"value":0};
                response.DATA.newPlayer[i] = {"item":dt,"value":0};
                dt = request.PUBFUN.getdatestr("ymd",0,1,bt);
            }
            DB.exeSql(`select DATE(etime) dt,COUNT(DISTINCT dkey) val from devlist where appkey = $1 AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY DATE(etime)`,(ret, err)=>{
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].dt);
                    for(let j=0;j<response.DATA.install.length;j++){
                        if(cdt == response.DATA.install[j].item){
                            response.DATA.install[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                DB.exeSql(`select DATE(etime) dt,COUNT(DISTINCT account) val from devusers where appkey = $1 AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY DATE(etime)`,(ret,err)=>{
                    for(let i=0;i<ret.rows.length;i++){
                        let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].dt);
                        for(let j=0;j<response.DATA.newPlayer.length;j++){
                            if(cdt == response.DATA.newPlayer[j].item){
                                response.DATA.newPlayer[j].value = ret.rows[i].val|0;
                                break;
                            }
                        }
                    }
                    rfun();
                },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/newPlayer/conversionList":{// 游戏玩家>新增玩家>玩家转化
            DataTmp(response.DATA = [], request.POST.startTime, request.POST.endTime);
            //console.time("begDB");
            DB.exeSql(`select DATE(A.etime) dt,COUNT(DISTINCT B.dkey)*1.0/COUNT(DISTINCT A.dkey) val from devlist A
            LEFT JOIN devusers B on A.appkey = B.appkey AND A.dkey = B.dkey
            where A.appkey = $1 AND A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY DATE(A.etime)`,(ret,err)=>{
                //console.timeEnd("begDB");
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].dt);
                    for(let j=0;j<response.DATA.length;j++){
                        if(cdt == response.DATA[j].item){
                            response.DATA[j].value = Number(ret.rows[i].val);
                            break;
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/newPlayer/subaccount":{ // 游戏玩家>新增玩家>单设备账户数量分析（小号分析）
            response.DATA = [{"item":"未注册","value":0},{"item":"1个","value":0},{"item":"2个","value":0},{"item":"3个","value":0},{"item":"4个","value":0},{"item":"5个","value":0},{"item":"6个","value":0},{"item":"7个","value":0},{"item":"8~10个","value":0},{"item":">10个","value":0}];
            DB.exeSql(`SELECT num,COUNT(*) val FROM (
                select A.dkey,COUNT(DISTINCT B.account) num from devlist A
                LEFT JOIN devusers B on A.appkey = B.appkey AND A.dkey = B.dkey
                where A.appkey = $1 AND A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY A.dkey
            ) X GROUP BY num`, (ret,err)=>{
                for(let i=0;i<ret.rows.length;i++){
                    if(ret.rows[i].num < 1) response.DATA[0].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 2) response.DATA[1].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 3) response.DATA[2].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 4) response.DATA[3].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 5) response.DATA[4].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 6) response.DATA[5].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 7) response.DATA[6].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 8) response.DATA[7].value = ret.rows[i].val|0;
                    else if(ret.rows[i].num < 11) response.DATA[8].value = ret.rows[i].val|0;
                    else response.DATA[9].value = ret.rows[i].val|0;
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/newPlayer/newPlayerList":{ // 游戏玩家>新增玩家> accountType:玩家账户类型 country:全球地区 partner:渠道 sex:性别 age:年龄 location:中国地区
            let gp = {accountType:"accountType",country:"area",partner:"channel",sex:"sex",age:"age",location:"area"};
            if(request.POST.groupBy && !(request.POST.groupBy in gp)){
                request.LOG.error("newPlayerList groupBy not ", request.POST, request.POST.groupBy);
                return;
            }
            let sql = `SELECT ${gp[request.POST.groupBy]} ite,COUNT(*) val FROM devusers WHERE appkey=$1 AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY ${gp[request.POST.groupBy]}`;
            if(request.POST.groupBy == "partner"){
                sql = `SELECT channel ite,COUNT(*) val FROM devusers A INNER JOIN devlist B ON A.appkey = B.appkey AND A.dkey = B.dkey WHERE A.appkey=$1 AND A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY channel`;
            }
            if(!request.POST.groupBy){
                sql = `SELECT ctime::date ite,COUNT(*) val FROM userbase WHERE appkey=$1 AND ctime >= $2 AND ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY ctime::date`;
            }
            DB.exeSql(sql, (ret,err)=>{
                //console.log(err);
                if(request.POST.groupBy == "age"){
                    response.DATA = [{"item":"未知","value":0},{"item":"15岁以下","value":0},{"item":"15~20岁","value":0},{"item":"20~25岁","value":0},{"item":"25~30岁","value":0},{"item":"30~35岁","value":0},{"item":"35~40岁","value":0},{"item":"40~45岁","value":0},{"item":"45~50岁","value":0},{"item":"50~60岁","value":0},{"item":"60岁以上","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 15) response.DATA[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 15 && ret.rows[i].ite < 20) response.DATA[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 20 && ret.rows[i].ite < 25) response.DATA[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 25 && ret.rows[i].ite < 30) response.DATA[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 30 && ret.rows[i].ite < 35) response.DATA[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 35 && ret.rows[i].ite < 40) response.DATA[6].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 40 && ret.rows[i].ite < 45) response.DATA[7].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 45 && ret.rows[i].ite < 50) response.DATA[8].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 50 && ret.rows[i].ite < 60) response.DATA[9].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 60) response.DATA[10].value += ret.rows[i].val|0;
                        else response.DATA[0].value += ret.rows[i].val|0;
                    }
                }else if(request.POST.groupBy){
                    response.DATA = [];
                    for(let i=0;i<ret.rows.length;i++){
                        response.DATA.push({"item":ret.rows[i].ite,"value":ret.rows[i].val|0});
                    }
                }else{
                    DataTmp(response.DATA = [], request.POST.startTime, request.POST.endTime);
                    for(let i=0;i<ret.rows.length;i++){
                        let dt = request.PUBFUN.getdatestr('ymd',0,0,ret.rows[i].ite);
                        for(let j=0;j<response.DATA.length;j++){
                            if(dt == response.DATA[j].item){
                                response.DATA[j].value = ret.rows[i].val|0;
                                break;
                            }
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime])
            break;
        }case "/game/v1/activity/newAndOldDauList":{ //游戏玩家>活跃玩家>DAU
            DataTmp(response.DATA = {"total":[],"old":[],"new":[]}, request.POST.startTime, request.POST.endTime);
            DB.exeSql(`SELECT DATE(etime) dt,COUNT(DISTINCT account) val,COUNT(CASE WHEN isNew = true THEN account ELSE NULL END) val2 FROM devusers WHERE appkey=$1 AND etime >= $2 AND etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY DATE(etime)`,(ret,err)=>{
                
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].dt);
                    for(let j=0;j<response.DATA.total.length;j++){
                        if(cdt == response.DATA.total[j].item){
                            response.DATA.total[j].value = ret.rows[i].val|0;
                            response.DATA.new[j].value = ret.rows[i].val2|0;
                            response.DATA.old[j].value = (ret.rows[i].val-ret.rows[i].val2)|0;
                            break;
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime])
            break;
        }case "/game/v1/activity/wauList":{ //游戏玩家>活跃玩家>WAU
            DataTmp(response.DATA = [], request.POST.startTime, request.POST.endTime);
            let sql = "";
            for(let i=0;i<response.DATA.length;i++){
                if(i == 0) sql = "(TO_DATE('"+response.DATA[i].item+"','YYMMDD'))";
                else sql += ",(TO_DATE('"+response.DATA[i].item+"','YYMMDD'))";
            }
            sql = `SELECT D dt,COUNT(DISTINCT account) val FROM (VALUES ${sql}) AS A(D) 
            LEFT JOIN devusers B ON A.D >= DATE(B.etime) AND DATE(B.etime) >= A.D - INTERVAL '7 day' AND B.appkey=$1
            GROUP BY D ORDER BY D`; //request.LOG.error(sql);
            DB.exeSql(sql,(ret,err)=>{
                //request.LOG.info(err, ret.rows);
                for(let j=0;j<ret.rows.length;j++){
                    response.DATA[j].value = ret.rows[j].val|0;
                }
                rfun();
            },[request.SESSION[request.POST.productID]]);
            break;
        }case "/game/v1/activity/mauList":{ //游戏玩家>活跃玩家>MAU
            DataTmp(response.DATA = [], request.POST.startTime, request.POST.endTime);
            let sql = "";
            for(let i=0;i<response.DATA.length;i++){
                if(i == 0) sql = "(TO_DATE('"+response.DATA[i].item+"','YYMMDD'))";
                else sql += ",(TO_DATE('"+response.DATA[i].item+"','YYMMDD'))";
            }
            sql = `SELECT D dt,COUNT(DISTINCT account) val FROM (VALUES ${sql}) AS A(D) 
            LEFT JOIN devusers B ON A.D >= DATE(B.etime) AND DATE(B.etime) >= A.D - INTERVAL '30 day' AND B.appkey=$1
            GROUP BY D ORDER BY D`; //request.LOG.error(sql);
            DB.exeSql(sql,(ret,err)=>{
                //request.LOG.error(err, ret);
                for(let j=0;j<ret.rows.length;j++){
                    response.DATA[j].value = ret.rows[j].val|0;
                }
                rfun();
            },[request.SESSION[request.POST.productID]]);
            // DB.exeSql("select * from devusers limit 1", (ret, err)=>{
            //     console.log(ret);
            // });
            break;
        }case "/game/v1/activity/dauDivideMauList":{ //游戏玩家>活跃玩家>DAU/MAU
            DataTmp(response.DATA = [], request.POST.startTime, request.POST.endTime);
            let sql = "";
            for(let i=0;i<response.DATA.length;i++){
                if(i == 0) sql = "(TO_DATE('"+response.DATA[i].item+"','YYMMDD'))";
                else sql += ",(TO_DATE('"+response.DATA[i].item+"','YYMMDD'))";
            }
            sql = `SELECT D dt,COUNT(DISTINCT CASE WHEN DATE(B.etime) = D THEN account ELSE NULL END) val,COUNT(DISTINCT account) val2 FROM (VALUES ${sql}) AS A(D) 
            LEFT JOIN devusers B ON A.D >= DATE(B.etime) AND DATE(B.etime) >= A.D - INTERVAL '30 day' AND B.appkey=$1
            GROUP BY D ORDER BY D`;
            DB.exeSql(sql, (ret,err)=>{
                for(let j=0;j<ret.rows.length;j++){
                    if((ret.rows[j].val|0) > 0) response.DATA[j].value = ret.rows[j].val/ret.rows[j].val2;
                }
                rfun();
            },[request.SESSION[request.POST.productID]])
            break;
        }case "/game/v1/activity/groupedPlayer":{ //游戏玩家>活跃玩家> lifetime:已玩天数 level:等级 country:国家 location:地区 partner:渠道 sex:性别 age:年龄
            let gp = {lifetime:"",level:"lev",country:"area",partner:"channel",sex:"sex",age:"age",location:"area","mobile":"model","mobileos":"os","pixel":"screen","carrier":"ext->>'isp'","network":"net"};
            if(!(request.POST.groupBy in gp)){
                request.LOG.error("groupBy not ", request.POST.groupBy);
                return;
            }
            let sql = `SELECT ${gp[request.POST.groupBy]} ite,COUNT(*) val FROM devusers WHERE appkey=$1 AND etime >= $2 AND etime <= TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY ${gp[request.POST.groupBy]}`;
            if(request.POST.groupBy == "partner" || request.POST.groupBy == "mobile" || request.POST.groupBy == "mobileos" || request.POST.groupBy == "pixel" || request.POST.groupBy == "carrier" || request.POST.groupBy == "network"){
                sql = `SELECT ${gp[request.POST.groupBy]} ite,COUNT(DISTINCT account) val FROM devusers A 
                INNER JOIN devlist B ON A.appkey = B.appkey AND A.dkey = B.dkey and A.etime > B.etime and EXTRACT(epoch from A.etime-B.etime) < 600
                WHERE A.appkey=$1 AND A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' GROUP BY ${gp[request.POST.groupBy]}`;
            }else if(request.POST.groupBy == "lifetime"){
                sql = `select ite,COUNT(*) val from (
                    select A.account,MAX(A.etime::date - B.etime::date) ite from devusers A 
                    INNER join devusers B ON A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' and A.appkey = B.appkey and A.account=B.account and B.isnew=true 
                    where A.appkey=$1 group by A.account 
                ) X group by ite`;
            }
            DB.exeSql(sql, (ret,err)=>{
                console.log(sql,ret);
                if(request.POST.groupBy == "age"){
                    response.DATA = [{"item":"未知","value":0},{"item":"15岁以下","value":0},{"item":"15~20岁","value":0},{"item":"20~25岁","value":0},{"item":"25~30岁","value":0},{"item":"30~35岁","value":0},{"item":"35~40岁","value":0},{"item":"40~45岁","value":0},{"item":"45~50岁","value":0},{"item":"50~60岁","value":0},{"item":"60岁以上","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 15) response.DATA[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 15 && ret.rows[i].ite < 20) response.DATA[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 20 && ret.rows[i].ite < 25) response.DATA[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 25 && ret.rows[i].ite < 30) response.DATA[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 30 && ret.rows[i].ite < 35) response.DATA[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 35 && ret.rows[i].ite < 40) response.DATA[6].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 40 && ret.rows[i].ite < 45) response.DATA[7].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 45 && ret.rows[i].ite < 50) response.DATA[8].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 50 && ret.rows[i].ite < 60) response.DATA[9].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 60) response.DATA[10].value += ret.rows[i].val|0;
                        else response.DATA[0].value += ret.rows[i].val|0;
                    }
                }else if(request.POST.groupBy == "lifetime"){
                    response.DATA = [{"item":"新用户","value":0},{"item":"2~3日","value":0},{"item":"4~7日","value":0},{"item":"8~14日","value":0},{"item":"15~30日","value":0},{"item":"31~90日","value":0},{"item":"91~180日","value":0},{"item":"181~365日","value":0},{"item":"1年+","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 2) response.DATA[0].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 4) response.DATA[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 8) response.DATA[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 15) response.DATA[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 31) response.DATA[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 91) response.DATA[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 181) response.DATA[6].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 366) response.DATA[7].value += ret.rows[i].val|0;
                        else response.DATA[8].value += ret.rows[i].val|0;
                    }
                }else{
                    response.DATA = [];
                    for(let i=0;i<ret.rows.length;i++){
                        response.DATA.push({"item":ret.rows[i].ite,"value":ret.rows[i].val|0});
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime])
            break;
        }case "/game/v1/retention/retentionList":{ //游戏玩家>玩家留存>新增账号/设备留存
            DataTmp(response.DATA = {"day01":[],"day30":[],"day07":[]},request.POST.startTime,request.POST.endTime);
            let sql = "";
            if(request.POST.retentionBean.type == "newPlayerRetention"){
                sql = `select A.etime::date,COUNT(distinct A.account) dall,COUNT(distinct case when B.etime::date-A.etime::date=1 then B.account else null end) da1, 
            COUNT(distinct case when B.etime::date-A.etime::date=7 then B.account else null end) da7,COUNT(distinct case when B.etime::date-A.etime::date=30 then B.account else null end) da30
            from devusers A 
            LEFT join devusers B ON A.isnew=true and A.account=B.account and (B.etime::date-A.etime::date = 1 or B.etime::date-A.etime::date = 7 or B.etime::date-A.etime::date = 30)
            where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
            group by A.etime::date`;
            }else{
                sql = `select A.etime::date,COUNT(distinct A.dkey) dall,COUNT(distinct case when B.etime::date-A.etime::date=1 then B.dkey else null end) da1, 
                COUNT(distinct case when B.etime::date-A.etime::date=7 then B.dkey else null end) da7,COUNT(distinct case when B.etime::date-A.etime::date=30 then B.dkey else null end) da30
            from devlist A 
            LEFT join devlist B ON A.isnew=true and A.dkey=B.dkey and (B.etime::date-A.etime::date = 1 or B.etime::date-A.etime::date = 7 or B.etime::date-A.etime::date = 30)
            where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
            group by A.etime::date`;
            }
            DB.exeSql(sql,(ret,err)=>{
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].etime);
                    for(let j=0;j<response.DATA.day01.length;j++){
                        if(cdt == response.DATA.day01[j].item){
                            response.DATA.day01[j].value = ret.rows[i].da1/ret.rows[i].dall*100;
                            response.DATA.day07[j].value = ret.rows[i].da7/ret.rows[i].dall*100;
                            response.DATA.day30[j].value = ret.rows[i].da30/ret.rows[i].dall*100;
                            break;
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/retention/retentionUserAnalys":{ //游戏玩家>玩家留存>留存用户分析
            let sql='';console.log(request.POST.groupBy);
            if(request.POST.groupBy == "firstDayLevel"){
                sql = `select ite,COUNT(*) val from (
                    select A.account,max(A.lev) ite from devusers A 
                    INNER join devusers B ON A.appkey=B.appkey and A.isnew=true and A.account=B.account and B.etime::date-A.etime::date = $4
                    where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                    group by A.account
                ) X group by ite`;
            }else if(request.POST.groupBy == "logintimes"){
                sql = `select ite,COUNT(*) val from (
                    select A.account,COUNT(A.lev) ite from devusers A 
                    INNER join devusers B ON A.appkey=B.appkey and A.isnew=true and A.account=B.account and B.etime::date-A.etime::date = $4
                    where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                    group by A.account
                ) X group by ite`;
            }else if(request.POST.groupBy == "firstDayUseTime"){
                sql = `select (case when sec<11 then '0-10S' when sec<61 then '11-60S' when sec<181 then '1-3mins' when sec<601 then '3-10mins' when sec<1801 then '10-30mins' when sec<3601 then '30-60mins' when sec<7201 then '1-2hours' when sec<14401 then '2-4hours' else '>4hours' end) ite,COUNT(*) val from(
                    select X.dkey,SUM(extract(epoch from case when etime is not null then etime-ctime else utime-ctime end)) sec from (
                        select A.dkey,MIN(A.etime) dt from devusers A 
                        INNER join devusers B ON A.appkey=B.appkey and A.isnew=true and A.account=B.account and B.etime::date-A.etime::date = $4
                        where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                        group by A.dkey
                    ) X INNER join devonline C on X.dkey=C.dkey group by X.dkey
                ) Y group by (case when sec<11 then '0-10S' when sec<61 then '11-60S' when sec<181 then '1-3mins' when sec<601 then '3-10mins' when sec<1801 then '10-30mins' when sec<3601 then '30-60mins' when sec<7201 then '1-2hours' when sec<14401 then '2-4hours' else '>4hours' end)`;
            }else if(request.POST.groupBy == "isFirstDayCharge"){
                sql = `select case when m>0 then '付费' else '未付费' end ite,COUNT(*) val from (
                    select A.account,SUM(C.edec) m from devusers A 
                    INNER join devusers B ON A.appkey=B.appkey and A.isnew=true and A.account=B.account and B.etime::date-A.etime::date = $4
                    LEFT join devevent C ON A.appkey=C.appkey and A.dkey=C.dkey and A.account=C.eparam
                    where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                    group by A.account
                ) X group by (case when m>0 then '付费' else '未付费' end)`;
            }else if(request.POST.groupBy == "sex"){
                sql = `select ite,COUNT(*) val from (
                    select A.account,max(A.sex) ite from devusers A 
                    INNER join devusers B ON A.appkey=B.appkey and A.isnew=true and A.account=B.account and B.etime::date-A.etime::date = $4
                    where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                    group by A.account
                ) X group by ite`;
            }else if(request.POST.groupBy == "age"){
                sql = `select ite,COUNT(*) val from (
                    select A.account,max(A.age) ite from devusers A 
                    INNER join devusers B ON A.appkey=B.appkey and A.isnew=true and A.account=B.account and B.etime::date-A.etime::date = $4
                    where A.isnew=true and A.appkey=$1 and A.etime >= $2 AND A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                    group by A.account
                ) X group by ite`;
            }
            DB.exeSql(sql,(ret)=>{
                if(request.POST.groupBy == "firstDayLevel"){
                    response.DATA = [{"item":"1","value":0},{"item":"2","value":0},{"item":"3","value":0},{"item":"4","value":0},{"item":"5","value":0},{"item":"6","value":0},{"item":"7","value":0},{"item":"8","value":0},{"item":"9","value":0},{"item":"10","value":0},{"item":"11","value":0},{"item":"12","value":0},{"item":"13","value":0},{"item":"14","value":0},{"item":"15","value":0},{"item":"16","value":0},{"item":"17","value":0},{"item":"18","value":0},{"item":"19","value":0},{"item":"20","value":0},{"item":"21","value":0},{"item":"22","value":0},{"item":"23","value":0},{"item":"24","value":0},{"item":"25","value":0},{"item":"26","value":0},{"item":"27","value":0},{"item":"28","value":0},{"item":"29","value":0},{"item":"30","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        for(let j=0;j<response.DATA.length;j++){
                            if(response.DATA[i].item == ret.rows[i].ite){
                                response.DATA[i].value = ret.rows[i].val|0;
                                break;
                            }
                        }
                    }
                }else if(request.POST.groupBy == "logintimes"){
                    response.DATA = [{"item":"1","value":0},{"item":"2-3","value":0},{"item":"4-5","value":0},{"item":"6-10","value":0},{"item":"11-20","value":0},{"item":"21-50","value":0},{"item":">50","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 2) response.DATA[0].value = ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 4) response.DATA[1].value = ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 6) response.DATA[2].value = ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 11) response.DATA[3].value = ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 21) response.DATA[4].value = ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 51) response.DATA[5].value = ret.rows[i].val|0;
                        else response.DATA[6].value = ret.rows[i].val|0;
                    }
                }else if(request.POST.groupBy == "firstDayUseTime"){
                    response.DATA = [{"item":"0-10S","value":0},{"item":"11-60S","value":0},{"item":"1-3mins","value":0},{"item":"3-10mins","value":0},{"item":"10-30mins","value":0},{"item":"30-60mins","value":0},{"item":"1-2hours","value":0},{"item":"2-4hours","value":0},{"item":">4hours","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        for(let j=0;j<response.DATA.length;j++){
                            if(response.DATA[j].item == ret.rows[i].ite){
                                response.DATA[j].value = ret.rows[i].val|0;
                                break;
                            }
                        }
                    }
                }else if(request.POST.groupBy == "isFirstDayCharge"){
                    response.DATA = [{"item":"付费","value":0},{"item":"未付费","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite == '付费') response.DATA[0].value = ret.rows[i].val|0;
                        else response.DATA[1].value = ret.rows[i].val|0;
                    }
                }else if(request.POST.groupBy == "sex"){
                    response.DATA = [];
                    for(let i=0;i<ret.rows.length;i++){
                        response.DATA.push({"item":ret.rows[i].ite,"value":ret.rows[i].val|0});
                    }
                }else if(request.POST.groupBy == "age"){
                    response.DATA = [{"item":"未知","value":0},{"item":"15岁以下","value":0},{"item":"15~20岁","value":0},{"item":"20~25岁","value":0},{"item":"25~30岁","value":0},{"item":"30~35岁","value":0},{"item":"35~40岁","value":0},{"item":"40~45岁","value":0},{"item":"45~50岁","value":0},{"item":"50~60岁","value":0},{"item":"60岁以上","value":0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 15) response.DATA[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 15 && ret.rows[i].ite < 20) response.DATA[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 20 && ret.rows[i].ite < 25) response.DATA[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 25 && ret.rows[i].ite < 30) response.DATA[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 30 && ret.rows[i].ite < 35) response.DATA[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 35 && ret.rows[i].ite < 40) response.DATA[6].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 40 && ret.rows[i].ite < 45) response.DATA[7].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 45 && ret.rows[i].ite < 50) response.DATA[8].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 50 && ret.rows[i].ite < 60) response.DATA[9].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite >= 60) response.DATA[10].value += ret.rows[i].val|0;
                        else response.DATA[0].value += ret.rows[i].val|0;
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime, request.POST.retentionBean.keepDay]);
            break;
        }case "/game/v1/convertion/newChargeUserNumList":{ //游戏玩家>付费转化>新增付费玩家
            DB.exeSql(`select ctime::date ite,COUNT(*) val from userbase where appkey=$1 and ctime >= $2 AND ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' and ctime::date=ptime::date group by ctime::date`, (ret, err)=>{
                DataTmp(response.DATA = [],request.POST.startTime,request.POST.endTime);
                //console.log(ret.rows);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.length;j++){
                        if(response.DATA[j].item == cdt){
                            response.DATA[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/chargeUserNumList":{ //游戏玩家>付费转化>累计付费玩家
            DB.exeSql(`select dt ite, val from (
                select dt,SUM(pfcount) OVER(ORDER BY dt) val FROM appbase where appkey=$1
            ) X where dt >= $2 and dt < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'`, (ret, err)=>{
                DataTmp(response.DATA = [],request.POST.startTime,request.POST.endTime);
                //console.log(ret.rows);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.length;j++){
                        if(response.DATA[j].item == cdt){
                            response.DATA[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/fetchTotalPaidRate":{ //游戏玩家>付费转化>总体付费率
            DB.exeSql(`select dt ite, val*100/v2 val from (
                select dt,SUM(ucount) OVER(ORDER BY dt) v2,SUM(pfcount) OVER(ORDER BY dt) val FROM appbase where appkey=$1
            ) X where dt >= $2 and dt < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'`, (ret, err)=>{
                DataTmp(response.DATA = [],request.POST.startTime,request.POST.endTime);
                //console.log(ret.rows);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.length;j++){
                        if(response.DATA[j].item == cdt){
                            response.DATA[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/fetchFirstDayChargeRateAndNum":{ //游戏玩家>付费转化>首日付费率
            DB.exeSql(`select ctime::date ite,COUNT(CASE WHEN ptime::date=ctime::date THEN account ELSE NULL END) num,COUNT(CASE WHEN ptime::date=ctime::date THEN account ELSE NULL END)*100.0/COUNT(*) val from userbase where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' group by ctime::date`, (ret, err)=>{
                DataTmp(response.DATA = {"num":[],"rate":[]},request.POST.startTime,request.POST.endTime);
                //console.log(ret.rows);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.num.length;j++){
                        if(response.DATA.num[j].item == cdt){
                            response.DATA.num[j].value = ret.rows[i].num|0;
                            response.DATA.rate[j].value = Number(Number(ret.rows[i].val).toFixed(2));
                            break;
                        }
                    }
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/fetchFirstWeekChargeRateAndNum":{ //游戏玩家>付费转化>首周付费率
            DB.exeSql(`select ctime::date ite,COUNT(CASE WHEN ptime::date-ctime::date<7 THEN account ELSE NULL END) num,COUNT(CASE WHEN ptime::date-ctime::date<7 THEN account ELSE NULL END)*100.0/COUNT(*) val from userbase where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' group by ctime::date`, (ret, err)=>{
                DataTmp(response.DATA = {"num":[],"rate":[]},request.POST.startTime,request.POST.endTime);
                //console.log(ret.rows);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.num.length;j++){
                        if(response.DATA.num[j].item == cdt){
                            response.DATA.num[j].value = ret.rows[i].num|0;
                            response.DATA.rate[j].value = Number(Number(ret.rows[i].val).toFixed(2));
                            break;
                        }
                    }
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/fetchFirstMonthChargeRateAndNum":{ //游戏玩家>付费转化>首月付费率
            DB.exeSql(`select ctime::date ite,COUNT(CASE WHEN ptime::date-ctime::date<30 THEN account ELSE NULL END) num,COUNT(CASE WHEN ptime::date-ctime::date<30 THEN account ELSE NULL END)*100.0/COUNT(*) val from userbase where appkey=$1 and ctime >= $2 and ctime <= TO_DATE($3,'YYMMDD') + INTERVAL '1 day' group by ctime::date`, (ret, err)=>{
                DataTmp(response.DATA = {"num":[],"rate":[]},request.POST.startTime,request.POST.endTime);
                //console.log(ret.rows);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.num.length;j++){
                        if(response.DATA.num[j].item == cdt){
                            response.DATA.num[j].value = ret.rows[i].num|0;
                            response.DATA.rate[j].value = Number(Number(ret.rows[i].val).toFixed(2));
                            break;
                        }
                    }
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/firstChargeTotalDayNumList":{ //游戏玩家>付费转化>游戏天数
            DB.exeSql(`select ptime::date-ctime::date ite,COUNT(*) val from userbase where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' and ptime is not null group by ptime::date-ctime::date`, (ret, err)=>{
                response.DATA = {"processed":[{"item":"首日","value":0},{"item":"2~3日","value":0},{"item":"4~7日","value":0},{"item":"2周","value":0},{"item":"3周","value":0},{"item":"4周","value":0},{"item":"5周","value":0},{"item":"6周","value":0},{"item":"7周","value":0},{"item":"8周","value":0},{"item":"9~12周","value":0},{"item":">12周","value":0}],"all":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]};
                for(let i=0;i<ret.rows.length;i++){
                    if(ret.rows[i].ite < 1) response.DATA.processed[0].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 3) response.DATA.processed[1].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 7) response.DATA.processed[2].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 14) response.DATA.processed[3].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 21) response.DATA.processed[4].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 28) response.DATA.processed[5].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 35) response.DATA.processed[6].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 42) response.DATA.processed[7].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 49) response.DATA.processed[8].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 56) response.DATA.processed[9].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 84) response.DATA.processed[10].value += ret.rows[i].val|0;
                    else response.DATA.processed[11].value += ret.rows[i].val|0;
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/firstChargeTotalTimeList":{ //游戏玩家>付费转化>累计游戏时长
            DB.exeSql(`select num ite,COUNT(*) val from(
                select A.account,ROUND(EXTRACT(epoch from SUM(B.utime-B.ctime))/600) num from userbase A 
                inner join devonline B on A.appkey=B.appkey and A.account=B.account and ptime is not null and ptime>utime
                where A.appkey=$1 and ptime >= $2 and ptime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' group by A.account
            ) X group by num`, (ret, err)=>{
                response.DATA = {"processed":[{"item":"<10分钟","value":0},{"item":"10~30分钟","value":0},{"item":"30~60分钟","value":0},{"item":"1~2小时","value":0},{"item":"2~4小时","value":0},{"item":"4~6小时","value":0},{"item":"6~10小时","value":0},{"item":"10~15小时","value":0},{"item":"15~20小时","value":0},{"item":"20~30小时","value":0},{"item":"30~40小时","value":0},{"item":"40~60小时","value":0},{"item":"60~100小时","value":0},{"item":">100小时","value":0}],"all":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]};
                for(let i=0;i<ret.rows.length;i++){
                    if(ret.rows[i].ite < 1) response.DATA.processed[0].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 3) response.DATA.processed[1].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 7) response.DATA.processed[2].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 13) response.DATA.processed[3].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 25) response.DATA.processed[4].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 37) response.DATA.processed[5].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 61) response.DATA.processed[6].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 91) response.DATA.processed[7].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 121) response.DATA.processed[8].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 181) response.DATA.processed[9].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 241) response.DATA.processed[10].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 361) response.DATA.processed[11].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 601) response.DATA.processed[12].value += ret.rows[i].val|0;
                    else response.DATA.processed[13].value += ret.rows[i].val|0;
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/firstChargeLevelList":{ //游戏玩家>付费转化>玩家首付等级
            DB.exeSql(`select num ite,COUNT(*) val from(
                select A.account,MAX(lev) num from userbase A 
                inner join devusers B on A.appkey=B.appkey and A.account=B.account and ptime is not null and A.ptime>B.etime
                where A.appkey=$1 and ptime >= $2 and ptime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' group by A.account
            ) X group by num`, (ret, err)=>{
                response.DATA = []; 
                for(let i=0;i<ret.rows.length;i++){
                    response.DATA.push({"item":ret.rows[i].ite,"value":ret.rows[i].val|0});
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/fetchFirstChargeMoney":{ //游戏玩家>付费转化>玩家首付金额
            DB.exeSql(`select pval ite,COUNT(*) val from userbase 
            where appkey=$1 and ptime is not null and ptime >= $2 and ptime <= TO_DATE($3,'YYMMDD') + INTERVAL '1 day' group by pval`, (ret, err)=>{
                response.DATA = [{"item":"<￥10","value":0},{"item":"￥10~50","value":0},{"item":"￥50~100","value":0},{"item":"￥100~200","value":0},{"item":"￥200~500","value":0},{"item":"￥500~1000","value":0},{"item":">￥1000","value":0}]; 
                for(let i=0;i<ret.rows.length;i++){
                    if(ret.rows[i].ite < 10) response.DATA[0].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 50) response.DATA[1].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 100) response.DATA[2].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 200) response.DATA[3].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 500) response.DATA[4].value += ret.rows[i].val|0;
                    else if(ret.rows[i].ite < 1000) response.DATA[5].value += ret.rows[i].val|0;
                    else response.DATA[6].value += ret.rows[i].val|0;
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/convertion/fetchFirstChargePackage":{ //游戏玩家>付费转化>首付充值包类型
            DB.exeSql(`SELECT A.eval ite,COUNT(*) val FROM devevent A 
            inner join (select eparam,MIN(etime) md from devevent where appkey=$1 and etime >= $2 and etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day' and ename='1' group by eparam) B ON A.eparam=B.eparam and A.etime=B.md
            group by A.eval`, (ret, err)=>{
                response.DATA = []; 
                for(let i=0;i<ret.rows.length;i++){
                    response.DATA.push({"item":ret.rows[i].ite,"value":ret.rows[i].val|0});
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/churned/fetchPerDayLostNumAndRate":{ //游戏玩家>玩家流失>每日流失
            let lostDay = 7;
            if(request.POST.churnedParameterBean.dayType == '14') lostDay = 14;
            else if(request.POST.churnedParameterBean.dayType == '30') lostDay = 30;
            DB.exeSql(`SELECT dt + interval '${lostDay} day' ite,COUNT(CASE WHEN lf>${lostDay-1} THEN acc ELSE null end) val,COUNT(*) num FROM(
                SELECT A.account acc,A.etime::date dt,CASE WHEN MIN(B.etime::date-A.etime::date) is null then now()::date-A.etime::date else MIN(B.etime::date-A.etime::date) end lf FROM devusers A 
                left join devusers B ON A.appkey=B.appkey and A.account=B.account and B.etime>A.etime
                where A.appkey=$1 and A.etime::date >= TO_DATE($2,'YYMMDD') - INTERVAL '${lostDay} day' and A.etime::date <= TO_DATE($3,'YYMMDD') - INTERVAL '${lostDay} day'
                group by A.account,A.etime::date
            ) X group by dt`, (ret, err)=>{ //console.log(ret);
                DataTmp(response.DATA = {"num":[],"rate":[]},request.POST.startTime,request.POST.endTime);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.num.length;j++){
                        if(response.DATA.num[j].item == cdt){
                            response.DATA.num[j].value = ret.rows[i].val|0;
                            response.DATA.rate[j].value = Number((ret.rows[i].val*100/ret.rows[i].num).toFixed(2));
                            break;
                        }
                    }
                }
                rfun();
            }, [request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/churned/perDayReturnNumList":{ //游戏玩家>玩家流失>每日回流
            let lostDay = 7;
            if(request.POST.churnedParameterBean.dayType == '14') lostDay = 14;
            else if(request.POST.churnedParameterBean.dayType == '30') lostDay = 30;
            DB.exeSql(`SELECT bdt ite,COUNT(*) val FROM(
                SELECT A.account acc,A.etime::date dt,MIN(B.etime::date) bdt FROM devusers A 
                left join devusers B ON A.appkey=B.appkey and A.account=B.account and B.etime>A.etime
                where A.appkey=$1 and B.etime >= $2 and A.etime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
                group by A.account,A.etime::date having MIN(B.etime::date)-A.etime::date >= ${lostDay}
            ) X group by bdt`,(ret,err)=>{
                DataTmp(response.DATA = [],request.POST.startTime,request.POST.endTime);
                for(let i=0;i<ret.rows.length;i++){
                    let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                    for(let j=0;j<response.DATA.length;j++){
                        if(response.DATA[j].item == cdt){
                            response.DATA[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/churned/lostUserAnalysNumList":{ //游戏玩家>玩家流失>流失用户分析 [流失前等级, 用户生命周期, 付费金额, 付费次数]
            let lostDay = 7, sql = '';
            if(request.POST.churnedParameterBean.dayType == '14') lostDay = 14;
            else if(request.POST.churnedParameterBean.dayType == '30') lostDay = 30;
            if(request.POST.churnedParameterBean.analysType == '2'){ //用户生命周期
                sql = `SELECT X.ctime::date-Y.dt ite,COUNT(DISTINCT X.account) val FROM userbase X inner join (
                    SELECT A.account,A.etime::date dt,MIN(B.etime) bdt FROM devusers A 
                    left join devusers B ON A.appkey=B.appkey and A.account=B.account and B.etime>A.etime
                    where A.appkey=$1 and A.etime::date >= TO_DATE($2,'YYMMDD') - INTERVAL '${lostDay} day' and A.etime::date <= TO_DATE($3,'YYMMDD') - INTERVAL '${lostDay} day'
                    group by A.account,A.etime::date having MIN(CASE WHEN B.etime is NULL THEN NOW() ELSE B.etime END::date-A.etime::date) > ${lostDay}
                ) Y on X.account=Y.account and X.appkey=$1
                group by X.ctime::date-Y.dt`;
            }else if(request.POST.churnedParameterBean.analysType == '3'){ //付费金额
                sql = `SELECT CASE WHEN num<10 THEN 0 WHEN num<50 THEN 1 WHEN num<100 THEN 2 WHEN num<200 THEN 3 WHEN num<500 THEN 4 WHEN num<1000 THEN 5 WHEN num<3000 THEN 6 WHEN num<5000 THEN 7 WHEN num<10000 THEN 8 ELSE 9 END ite,COUNT(acc) val FROM(
                    SELECT Y.account acc,SUM(CASE WHEN X.edec IS NULL THEN 0 ELSE X.edec END) num FROM (
                        SELECT A.account,MIN(B.etime) bdt FROM devusers A 
                        left join devusers B ON A.appkey=B.appkey and A.account=B.account and B.etime>A.etime
                        where A.appkey=$1 and A.etime::date >= TO_DATE($2,'YYMMDD') - INTERVAL '${lostDay} day' and A.etime::date <= TO_DATE($3,'YYMMDD') - INTERVAL '${lostDay} day'
                        group by A.account having MIN(CASE WHEN B.etime is NULL THEN NOW() ELSE B.etime END::date-A.etime::date) > ${lostDay}
                    ) Y LEFT JOIN devevent X on X.eparam=Y.account and ename='1' and X.appkey=$1
                    group by Y.account
                ) Z group by CASE WHEN num<10 THEN 0 WHEN num<50 THEN 1 WHEN num<100 THEN 2 WHEN num<200 THEN 3 WHEN num<500 THEN 4 WHEN num<1000 THEN 5 WHEN num<3000 THEN 6 WHEN num<5000 THEN 7 WHEN num<10000 THEN 8 ELSE 9 END`;
            }else if(request.POST.churnedParameterBean.analysType == '4'){ //付费次数
                sql = `SELECT num ite,COUNT(acc) val FROM(
                    SELECT Y.account acc,COUNT(X.edec) num FROM (
                        SELECT A.account,MIN(B.etime) bdt FROM devusers A 
                        left join devusers B ON A.appkey=B.appkey and A.account=B.account and B.etime>A.etime
                        where A.appkey=$1 and A.etime::date >= TO_DATE($2,'YYMMDD') - INTERVAL '${lostDay} day' and A.etime::date <= TO_DATE($3,'YYMMDD') - INTERVAL '${lostDay} day'
                        group by A.account having MIN(CASE WHEN B.etime is NULL THEN NOW() ELSE B.etime END::date-A.etime::date) > ${lostDay}
                    ) Y LEFT JOIN devevent X on X.eparam=Y.account and ename='1' and X.appkey=$1
                    group by Y.account
                ) Z group by num`;
            }else{ //流失前等级
                sql = `SELECT lev ite,COUNT(account) val FROM(
                SELECT X.account,MAX(X.lev) lev FROM devusers X inner join (
                    SELECT A.account,A.etime::date dt,MIN(B.etime) bdt FROM devusers A 
                    left join devusers B ON A.appkey=B.appkey and A.account=B.account and B.etime>A.etime
                    where A.appkey=$1 and A.etime::date >= TO_DATE($2,'YYMMDD') - INTERVAL '${lostDay} day' and A.etime::date <= TO_DATE($3,'YYMMDD') - INTERVAL '${lostDay} day'
                    group by A.account,A.etime::date having CASE WHEN MIN(B.etime::date-A.etime::date) is null then now()::date-A.etime::date else MIN(B.etime::date-A.etime::date) end > ${lostDay}
                ) Y on X.account=Y.account and X.appkey=$1 and (X.etime < Y.bdt or Y.bdt is NULL)
                group by X.account
            ) Z group by lev`;
            }
            DB.exeSql(sql,(ret,err)=>{
                if(request.POST.churnedParameterBean.analysType == '2'){
                    response.DATA = [{"item":"新用户","value":0.0},{"item":"2~3日","value":0.0},{"item":"4~7日","value":0.0},{"item":"8~14日","value":0.0},{"item":"15~30日","value":0.0},{"item":"31~90日","value":0.0},{"item":"91~180日","value":0.0},{"item":"181~365日","value":0.0},{"item":"1年+","value":0.0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 2) response.DATA[0].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 4) response.DATA[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 8) response.DATA[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 15) response.DATA[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 31) response.DATA[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 91) response.DATA[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 181) response.DATA[6].value += ret.rows[i].val|0;
                        else response.DATA[7].value += ret.rows[i].val|0;
                    }
                }else if(request.POST.churnedParameterBean.analysType == '3'){
                    response.DATA = [{"item":"<￥10","value":0.0},{"item":"￥10~50","value":0.0},{"item":"￥50~100","value":0.0},{"item":"￥100~200","value":0.0},{"item":"￥200~500","value":0.0},{"item":"￥500~1000","value":0.0},{"item":"￥1000~3000","value":0.0},{"item":"￥3000~5000","value":0.0},{"item":"￥5000~10000","value":0.0},{"item":">￥10000","value":0.0}];
                    for(let i=0;i<ret.rows.length;i++){
                        response.DATA[ret.rows[i].ite].value += ret.rows[i].val|0;
                    }
                }else if(request.POST.churnedParameterBean.analysType == '4'){
                    response.DATA = [{"item":"未付费","value":0.0},{"item":"1次","value":0.0},{"item":"2次","value":0.0},{"item":"3次","value":0.0},{"item":"4次","value":0.0},{"item":"5次","value":0.0},{"item":"6~10次","value":0.0},{"item":"11~20次","value":0.0},{"item":"21~30次","value":0.0},{"item":"31~40次","value":0.0},{"item":"41~50次","value":0.0},{"item":">50次","value":0.0}];
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 1) response.DATA[0].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 2) response.DATA[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 3) response.DATA[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 4) response.DATA[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 5) response.DATA[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 6) response.DATA[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 11) response.DATA[6].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 21) response.DATA[7].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 31) response.DATA[8].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 41) response.DATA[9].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 51) response.DATA[10].value += ret.rows[i].val|0;
                        else response.DATA[11].value += ret.rows[i].val|0;
                    }
                }else{
                    response.DATA = [];
                    for(let i=0;i<ret.rows.length;i++){
                        response.DATA.push({"item":ret.rows[i].ite,"value":ret.rows[i].val|0});
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/behavior/avgGameTimesList":{ //游戏玩家>游戏习惯>平均游戏时长与次数
            let sql = "";
            if(request.POST.player.dayType == 'week'){
                sql = `SELECT EXTRACT(week from ctime) ite,COUNT(DISTINCT account) unum,COUNT(*) rnum,EXTRACT(epoch from SUM(utime-ctime)) otime from devonline
            where appkey=$1 and EXTRACT(week from ctime) >= EXTRACT(week from TO_DATE($2,'YYMMDD')) and EXTRACT(week from ctime) <= EXTRACT(week from TO_DATE($3,'YYMMDD'))
            group by EXTRACT(week from ctime)`;
            }else if(request.POST.player.dayType == 'month'){
                sql = `SELECT EXTRACT(month from ctime) ite,COUNT(DISTINCT account) unum,COUNT(*) rnum,EXTRACT(epoch from SUM(utime-ctime)) otime from devonline
            where appkey=$1 and EXTRACT(month from ctime) >= EXTRACT(month from TO_DATE($2,'YYMMDD')) and EXTRACT(month from ctime) <= EXTRACT(month from TO_DATE($3,'YYMMDD'))
            group by EXTRACT(month from ctime)`;
            }else{
                sql = `SELECT ctime::date ite,COUNT(DISTINCT account) unum,COUNT(*) rnum,EXTRACT(epoch from SUM(utime-ctime)) otime from devonline
            where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
            group by ctime::date`;
            }
            DB.exeSql(sql, (ret,err)=>{
                if(request.POST.player.dayType == 'week'){
                    let fwn = DataTmpWeek(response.DATA = {"useTimeList":[],"dauList":[],"useTimesList":[]},request.POST.startTime,request.POST.endTime);
                    for(let i=0;i<ret.rows.length;i++){
                        response.DATA.useTimeList[ret.rows[i].ite-fwn].value = ret.rows[i].otime|0;
                        response.DATA.dauList[ret.rows[i].ite-fwn].value = ret.rows[i].unum|0;
                        response.DATA.useTimesList[ret.rows[i].ite-fwn].value = ret.rows[i].rnum|0;
                    }
                }else if(request.POST.player.dayType == 'month'){
                    DataTmpMonth(response.DATA = {"useTimeList":[],"dauList":[],"useTimesList":[]},request.POST.startTime,request.POST.endTime);
                    for(let i=0;i<ret.rows.length;i++){
                        for(let j=0;j<response.DATA.useTimeList.length;j++){
                            if(response.DATA.useTimeList[j].item == ret.rows[i].ite){
                                response.DATA.useTimeList[j].value = ret.rows[i].otime|0;
                                response.DATA.dauList[j].value = ret.rows[i].unum|0;
                                response.DATA.useTimesList[j].value = ret.rows[i].rnum|0;
                                break;
                            }
                        }
                    }
                }else{
                    DataTmp(response.DATA = {"useTimeList":[],"dauList":[],"useTimesList":[]},request.POST.startTime,request.POST.endTime);
                    for(let i=0;i<ret.rows.length;i++){
                        let cdt = request.PUBFUN.getdatestr("ymd",0,0,ret.rows[i].ite);
                        for(let j=0;j<response.DATA.useTimeList.length;j++){
                            if(response.DATA.useTimeList[j].item == cdt){
                                response.DATA.useTimeList[j].value = ret.rows[i].otime|0;
                                response.DATA.dauList[j].value = ret.rows[i].unum|0;
                                response.DATA.useTimesList[j].value = ret.rows[i].rnum|0;
                                break;
                            }
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }case "/game/v1/behavior/gameFrequencyList":{ //游戏玩家>游戏习惯>游戏频次
            response.DATA = {"title":"",list:[]};
            let ed = new Date("20"+request.POST.endTime.substr(0,2)+"-"+request.POST.endTime.substr(2,2)+"-"+request.POST.endTime.substr(4,2));
            ed.setMonth(ed.getMonth()+1);
            ed.setDate(0);
            response.DATA.title = "20"+request.POST.startTime.substr(0,2)+request.POST.startTime.substr(2,2)+"01~";
            response.DATA.title += request.PUBFUN.getdatestr("Ymd",0,0,ed);
            response.DATA.title += "的2个自然月内汇总数量";
            let sql = "";
            if(request.POST.player.groupType == 'monthGameDays'){
                sql = `SELECT ite,COUNT(*) val FROM(
                    SELECT account,AVG(ite)::integer ite FROM(
                        SELECT EXTRACT(month from ctime) mon,account,COUNT(DISTINCT ctime::date) ite from devonline
                        where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD')
                        group by EXTRACT(month from ctime),account
                    ) X group by account
                ) Y group by ite`;
            }else if(request.POST.player.groupType == 'weekGameDays'){
                sql = `SELECT ite,COUNT(*) val FROM(
                    SELECT account,AVG(ite)::integer ite FROM(
                        SELECT EXTRACT(week from ctime) mon,account,COUNT(DISTINCT ctime::date) ite from devonline
                        where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD')
                        group by EXTRACT(week from ctime),account
                    ) X group by account
                ) Y group by ite`;
            }else if(request.POST.player.groupType == 'weekGameTimes'){
                sql = `SELECT ite,COUNT(*) val FROM(
                    SELECT account,AVG(ite)::integer ite FROM(
                        SELECT EXTRACT(week from ctime) mon,account,COUNT(*) ite from devonline
                        where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD')
                        group by EXTRACT(week from ctime),account
                    ) X group by account
                ) Y group by ite`;
            }else if(request.POST.player.groupType == 'dayGameTimes'){
                sql = `SELECT ite,COUNT(*) val FROM(
                    SELECT account,AVG(ite)::integer ite FROM(
                        SELECT ctime::date,account,COUNT(*) ite from devonline
                        where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD')
                        group by ctime::date,account
                    ) X group by account
                ) Y group by ite`;
            }
            DB.exeSql(sql,(ret,err)=>{
                if(request.POST.player.groupType == 'monthGameDays'){
                    response.DATA.list = [{"item":"1","value":0},{"item":"2","value":0},{"item":"3","value":0},{"item":"4","value":0},{"item":"5","value":0},{"item":"6","value":0},{"item":"7","value":0},{"item":"8~14","value":0},{"item":"15~21","value":0},{"item":"22~30","value":0}];
                }else if(request.POST.player.groupType == 'weekGameDays'){
                    response.DATA.list = [{"item":"1","value":0},{"item":"2","value":0},{"item":"3","value":0},{"item":"4","value":0},{"item":"5","value":0},{"item":"6","value":0},{"item":"7","value":0}];
                }else if(request.POST.player.groupType == 'weekGameTimes'){
                    response.DATA.list = [{"item":"1","value":0},{"item":"2~3","value":0},{"item":"4~5","value":0},{"item":"6~10","value":0},{"item":"11~20","value":0},{"item":"21~50","value":0},{"item":"50~100","value":0},{"item":"100~200","value":0},{"item":">200","value":0}];
                }else if(request.POST.player.groupType == 'dayGameTimes'){
                    response.DATA.list = [{"item":"1","value":0},{"item":"2~3","value":0},{"item":"4~5","value":0},{"item":"6~10","value":0},{"item":"11~20","value":0},{"item":"21~50","value":0},{"item":">50","value":0}];
                }
                if(request.POST.player.groupType == 'monthGameDays' || request.POST.player.groupType == 'weekGameDays'){
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 2) response.DATA.list[0].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 3) response.DATA.list[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 4) response.DATA.list[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 5) response.DATA.list[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 6) response.DATA.list[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 7) response.DATA.list[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 15) response.DATA.list[6].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 22) response.DATA.list[7].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 31) response.DATA.list[8].value += ret.rows[i].val|0;
                    }
                }else if(request.POST.player.groupType == 'weekGameTimes'){
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 2) response.DATA.list[0].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 4) response.DATA.list[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 6) response.DATA.list[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 11) response.DATA.list[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 21) response.DATA.list[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 50) response.DATA.list[5].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 100) response.DATA.list[6].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 201) response.DATA.list[7].value += ret.rows[i].val|0;
                        else response.DATA.list[8].value += ret.rows[i].val|0;
                    }
                }else if(request.POST.player.groupType == 'dayGameTimes'){
                    for(let i=0;i<ret.rows.length;i++){
                        if(ret.rows[i].ite < 2) response.DATA.list[0].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 4) response.DATA.list[1].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 6) response.DATA.list[2].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 11) response.DATA.list[3].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 21) response.DATA.list[4].value += ret.rows[i].val|0;
                        else if(ret.rows[i].ite < 50) response.DATA.list[5].value += ret.rows[i].val|0;
                        else response.DATA.list[6].value += ret.rows[i].val|0;
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], '20'+request.POST.startTime.substr(0,4)+'01', request.PUBFUN.getdatestr("ymd",0,1,ed)])
            break;
        }case "/game/v1/behavior/avgGameDurationList":{ //游戏玩家>游戏习惯>游戏时长
            response.DATA = {"title":"",list:[]};
            let ed = new Date("20"+request.POST.endTime.substr(0,2)+"-"+request.POST.endTime.substr(2,2)+"-"+request.POST.endTime.substr(4,2));
            ed.setMonth(ed.getMonth()+1);
            ed.setDate(0);
            response.DATA.title = "20"+request.POST.startTime.substr(0,2)+request.POST.startTime.substr(2,2)+"01~";
            response.DATA.title += request.PUBFUN.getdatestr("Ymd",0,0,ed);
            response.DATA.title += "的2个自然月内汇总数量";
            let sql = "";
            if(request.POST.player.groupType == 'weekUseTime'){
                sql = `SELECT CASE WHEN ite<60 THEN '<60s' WHEN ite<181 THEN '1~3mins' WHEN ite<601 THEN '3~10mins' WHEN ite<1801 THEN '10~30mins' WHEN ite<3601 THEN '30~60mins' WHEN ite<7201 THEN '1~2hours' WHEN ite<14401 THEN '2~4hours' WHEN ite<21601 THEN '4~6hours' WHEN ite<36001 THEN '6~10hours' WHEN ite<54001 THEN '10~15hours' WHEN ite<72001 THEN '15~20hours' ELSE '>20hours' END ite,COUNT(*) val FROM(
                    SELECT account,AVG(ite)::integer ite FROM(
                        SELECT EXTRACT(week from ctime) mon,account,SUM(EXTRACT(epoch from utime-ctime)) ite from devonline
                        where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD')
                        group by EXTRACT(week from ctime),account
                    ) X group by account
                ) Y group by CASE WHEN ite<60 THEN '<60s' WHEN ite<181 THEN '1~3mins' WHEN ite<601 THEN '3~10mins' WHEN ite<1801 THEN '10~30mins' WHEN ite<3601 THEN '30~60mins' WHEN ite<7201 THEN '1~2hours' WHEN ite<14401 THEN '2~4hours' WHEN ite<21601 THEN '4~6hours' WHEN ite<36001 THEN '6~10hours' WHEN ite<54001 THEN '10~15hours' WHEN ite<72001 THEN '15~20hours' ELSE '>20hours' END`;
            }else if(request.POST.player.groupType == 'dayUseTime'){
                sql = `SELECT CASE WHEN ite<10 THEN '<10s' WHEN ite<61 THEN '11~60s' WHEN ite<181 THEN '1~3mins' WHEN ite<601 THEN '3~10mins' WHEN ite<1801 THEN '10~30mins' WHEN ite<3601 THEN '30~60mins' WHEN ite<7201 THEN '1~2hours' WHEN ite<14401 THEN '2~4hours' ELSE '>4hours' END ite,COUNT(*) val FROM(
                    SELECT account,AVG(ite)::integer ite FROM(
                        SELECT ctime::date,account,SUM(EXTRACT(epoch from utime-ctime)) ite from devonline
                        where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD')
                        group by ctime::date,account
                    ) X group by account
                ) Y group by CASE WHEN ite<10 THEN '<10s' WHEN ite<61 THEN '11~60s' WHEN ite<181 THEN '1~3mins' WHEN ite<601 THEN '3~10mins' WHEN ite<1801 THEN '10~30mins' WHEN ite<3601 THEN '30~60mins' WHEN ite<7201 THEN '1~2hours' WHEN ite<14401 THEN '2~4hours' ELSE '>4hours' END`;
            }else if(request.POST.player.groupType == 'perTimeUseTime'){
                sql = `SELECT CASE WHEN ite<4 THEN '1~3S' WHEN ite<11 THEN '4~10S' WHEN ite<31 THEN '11~30S' WHEN ite<61 THEN '31~60S' WHEN ite<181 THEN '1~3min' WHEN ite<601 THEN '3~10min' WHEN ite<1801 THEN '10~30min' WHEN ite<3601 THEN '30~60min' ELSE '>1hour' END ite,COUNT(*) val FROM(
                    SELECT EXTRACT(epoch from utime-ctime) ite from devonline
                    where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD')
                ) Y group by CASE WHEN ite<4 THEN '1~3S' WHEN ite<11 THEN '4~10S' WHEN ite<31 THEN '11~30S' WHEN ite<61 THEN '31~60S' WHEN ite<181 THEN '1~3min' WHEN ite<601 THEN '3~10min' WHEN ite<1801 THEN '10~30min' WHEN ite<3601 THEN '30~60min' ELSE '>1hour' END`;
            }
            DB.exeSql(sql, (ret,err)=>{
                if(request.POST.player.groupType == 'weekUseTime'){
                    response.DATA.list = [{"item":"<60s","value":0},{"item":"1~3mins","value":0},{"item":"3~10mins","value":0},{"item":"10~30mins","value":0},{"item":"30~60mins","value":0},{"item":"1~2hours","value":0},{"item":"2~4hours","value":0},{"item":"4~6hours","value":0},{"item":"6~10hours","value":0},{"item":"10~15hours","value":0},{"item":"15~20hours","value":0},{"item":">20hours","value":0},{"item":"未知","value":0}];
                }else if(request.POST.player.groupType == 'dayUseTime'){
                    response.DATA.list = [{"item":"<10s","value":0},{"item":"11~60s","value":0},{"item":"1~3mins","value":0},{"item":"3~10mins","value":0},{"item":"10~30mins","value":0},{"item":"30~60mins","value":0},{"item":"1~2hours","value":0},{"item":"2~4hours","value":0},{"item":">4hours","value":0},{"item":"未知","value":0}];
                }else if(request.POST.player.groupType == 'perTimeUseTime'){
                    response.DATA.list = [{"item":"1~3S","value":0},{"item":"4~10S","value":0},{"item":"11~30S","value":0},{"item":"31~60S","value":0},{"item":"1~3min","value":0},{"item":"3~10min","value":0},{"item":"10~30min","value":0},{"item":"30~60min","value":0},{"item":">1hour","value":0},{"item":"未知","value":0}];
                }
                for(let i=0;i<ret.rows.length;i++){
                    for(let j=0;j<response.DATA.list.length;j++){
                        if(response.DATA.list[j].item == ret.rows[i].ite){
                            response.DATA.list[j].value = ret.rows[i].val|0;
                            break;
                        }
                    }
                }
                rfun();
            },[request.SESSION[request.POST.productID], '20'+request.POST.startTime.substr(0,4)+'01', request.PUBFUN.getdatestr("ymd",0,1,ed)]);
            break;
        }case "/game/v1/behavior/gameTimesList":{ //游戏玩家>游戏习惯>游戏时段
            DB.exeSql(`SELECT EXTRACT(hour from ctime) ite,COUNT(DISTINCT account) val from devonline
            where appkey=$1 and ctime >= $2 and ctime < TO_DATE($3,'YYMMDD') + INTERVAL '1 day'
            group by EXTRACT(hour from ctime)`, (ret,err)=>{
                response.DATA = [{"item":"0:00~0:59","value":0},{"item":"1:00~1:59","value":0},{"item":"2:00~2:59","value":0},{"item":"3:00~3:59","value":0},{"item":"4:00~4:59","value":0},{"item":"5:00~5:59","value":0},{"item":"6:00~6:59","value":0},{"item":"7:00~7:59","value":0},{"item":"8:00~8:59","value":0},{"item":"9:00~9:59","value":0},{"item":"10:00~10:59","value":0},{"item":"11:00~11:59","value":0},{"item":"12:00~12:59","value":0},{"item":"13:00~13:59","value":0},{"item":"14:00~14:59","value":0},{"item":"15:00~15:59","value":0},{"item":"16:00~16:59","value":0},{"item":"17:00~17:59","value":0},{"item":"18:00~18:59","value":0},{"item":"19:00~19:59","value":0},{"item":"20:00~20:59","value":0},{"item":"21:00~21:59","value":0},{"item":"22:00~22:59","value":0},{"item":"23:00~23:59","value":0}];
                for(let i=0;i<ret.rows.length;i++){
                    response.DATA[ret.rows[i].ite].value = ret.rows[i].val|0;
                }
                rfun();
            },[request.SESSION[request.POST.productID], request.POST.startTime, request.POST.endTime]);
            break;
        }default:{
            request.LOG.error("not find", request.GET.pathname);
            rfun();
        }
    }
}

function DataTmp(da, bd, ed){
    const pubfun = require("../pubfun");
    let b = bd, bt = new Date("20"+bd.substr(0,2)+"-"+bd.substr(2,2)+"-"+bd.substr(4,2));
    if(da.length === 0){
        for(let i=0;b<=ed;i++){
            da[i] = {"item":b,"value":0};
            b = pubfun.getdatestr("ymd",0,1,bt);
        }
    }else{
        for(let k in da){
            if(da[k].length === 0){
                b = bd, bt = new Date("20"+bd.substr(0,2)+"-"+bd.substr(2,2)+"-"+bd.substr(4,2));
                for(let i=0;b<=ed;i++){
                    da[k][i] = {"item":b,"value":0};
                    b = pubfun.getdatestr("ymd",0,1,bt);
                }
            }
        }
    }
}
function DataTmpWeek(da, bd, ed){
    const pubfun = require("../pubfun");
    let b = bd, bt = new Date("20"+bd.substr(0,2)+"-"+bd.substr(2,2)+"-"+bd.substr(4,2)), fw = new Date("20"+bd.substr(0,2)+"-01-04"), wn = 1;
    if(bt.getDay() != 1){
        if(bt.getDay() == 0) pubfun.getdatestr("ymd",0,-6,bt);
        else pubfun.getdatestr("ymd",0,-bt.getDay()+1,bt);
    }
    wn = Math.ceil(((bt.getTime()-fw.getTime())/86400000+fw.getDay()+1)/7);
    if(da.length === 0){
        for(let i=0;b<=ed;i++){
            let edt = pubfun.getdatestr("ymd",0,6,bt);
            if(edt > ed) edt = ed;
            da[i] = {"item":b+"~"+edt,"value":0};
            b = pubfun.getdatestr("ymd",0,1,bt);
        }
    }else{
        for(let k in da){
            if(da[k].length === 0){
                b = bd, bt = new Date("20"+bd.substr(0,2)+"-"+bd.substr(2,2)+"-"+bd.substr(4,2));
                for(let i=0;b<=ed;i++){
                    let edt = pubfun.getdatestr("ymd",0,6,bt);
                    if(edt > ed) edt = ed;
                    da[k][i] = {"item":b+"~"+edt,"value":0};
                    b = pubfun.getdatestr("ymd",0,1,bt);
                }
            }
        }
    }
    return wn;
}
function DataTmpMonth(da, bd, ed){
    const pubfun = require("../pubfun");//console.log(typeof(bd));
    let b = bd, bt = new Date("20"+bd.substr(0,2)+"-"+bd.substr(2,2)+"-01");
    if(da.length === 0){
        for(let i=0;b<=ed;i++){
            da[i] = {"item":pubfun.getdatestr("m",0,0,bt),"value":0};
            b = pubfun.getdatestr("ymd",1,0,bt);
        }
    }else{
        for(let k in da){
            if(da[k].length === 0){
                b = bd, bt = new Date("20"+bd.substr(0,2)+"-"+bd.substr(2,2)+"-01");
                for(let i=0;b<=ed;i++){
                    da[k][i] = {"item":pubfun.getdatestr("m",0,0,bt),"value":0};
                    b = pubfun.getdatestr("ymd",1,0,bt);
                }
            }
        }
    }
}